package com.apes.hr.paySupport.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.hr.paySupport.model.SupportApplication;
import com.apes.hr.paySupport.model.SupportApplicationItem;
import com.apes.scm.masterdata.enumeration.model.Enumeration;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

/**
 * @Title: SupportApplication
 * @Description:付费支援申请单
 * @author: ZhengZeXian
 * @create: 2021/4/15 10:07
 */
public interface SupportApplicationRepository extends CustomRepository<SupportApplication,String> {

    @Query(value = "SELECT s.* FROM hr_support_application s WHERE s.id = (SELECT t.support_application_id FROM hr_support_application_item t WHERE t.id = :id)",nativeQuery = true)
    SupportApplication findBySupportApplicationItemId(@Param("id") String id);

    @Query("select s " +
            "from SupportApplication s where (s.lastUpdatedStamp > :times and s.state ='done') or (s.id > :id and s.lastUpdatedStamp = :times and s.state ='done')  " +
            " order by s.lastUpdatedStamp, s.id asc ")
    List<SupportApplication> findAllSupportApplicationByTs(@Param("times") Date times, @Param("id") String id, Pageable pageable);

    @Query("SELECT B.operation FROM Corporate B WHERE B.party.id = " +
            "(SELECT C.party.id FROM PartyRole C WHERE 1 = 1 AND C.id = " +
            "   (SELECT D.company.id FROM Dept D WHERE D.id = " +
            "       (SELECT MAX(E.id) FROM Dept E WHERE E.branch.id = :id)))")
    Enumeration findOperation(@Param("id") String id);

    @Query("SELECT B.fnTag FROM Store B WHERE B.dept.id = :id")
    Enumeration findFnTag(@Param("id") String id);
}
